#! /bin/bash
export LANG=zh_CN.UTF-8
HIVE_HOME=/export/server/presto/bin/presto


${PRESTO_HOME} --catalog hive --server 192.168.88.80.8090 --execute "


insert into hive.edu_dwb.hcx_dwb_customer_relationship_wide
select
       cr.id as id,
       cr.create_date_time as create_date_time,
       cr.deleted as deleted,
       cr.customer_id as customer_id,
       case when   cr.origin_type IN ('NETSERVICE', 'PRESIGNUP')  then 'online' else 'other' end as origin_type ,
       cr.origin_channel as origin_channel,
       cr.creator as creator,
       cr.itcast_school_id as itcast_school_id,
       cr.itcast_subject_id as itcast_subject_id,
       cc.customer_relationship_id as customer_relationship_id,
       case when cc.clue_state='INVALID_PUBLIC_OLD_CLUE' then 'old' else 'new' end as clue_state,
       cus.id as cus_id,
       cus.area as cus_area,
       emp.id as emp_id,
       emp.tdepart_id as department_id,
       dep.name as department_name,
       sch.id as sch_id,
       sch.name as scj_name,
       sub.id as sub_id,
       sub.name as sub_name
from hive.edu_dwd.hcx_fact_dwd_customer_relationship cr
left join hive.edu_dwd.hcx_dim_dwd_customer_clue cc on cc.customer_relationship_id=cr.id
left join hive.edu_dwd.hcx_dim_dwd_customer cus on cus.id=cr.customer_id
left join hive.edu_dwd.hcx_dim_dwd_employee emp  on cr.creator=emp.id
left join hive.edu_dwd.hcx_dim_dwd_department dep on dep.id=emp.tdepart_id
left join hive.edu_dwd.hcx_dim_dwd_itcast_school sch on cr.itcast_school_id=sch.id
left join hive.edu_dwd.hcx_dim_dwd_itcast_subject sub on sub.id=cr.itcast_subject_id
;

insert into hive.edu_dwb.hcx_dwb_customer_clue_wide;
select cc.id                                                                                  as id,
       cc.customer_relationship_id                                                            as customer_relationship_id,
       cc.create_date_time                                                                    as create_date_time,
       case when clue_state = 'INVALID_PUBLIC_OLD_CLUE' then 'old' else 'new' end             as clue_state,
       cr.id                                                                                  as cr_id,
       cr.deleted                                                                             as delited,
       case when cr.origin_type IN ('NETSERVICE', 'PRESIGNUP') then 'online' else 'other' end as origin_type,
        app.customer_relationship_first_id                                                     as customer_relationship_first_id,

        case
            when app.appeal_status = 0 then '待稽核'
            when app.appeal_status = 1 then '无效'
            when app.appeal_status = 2 then '有效' end                     as appeal_status

from hive.edu_dwd.hcx_fact_dwd_customer_clue cc
full join hive.edu_dwd.hcx_dim_dwd_customer_relationship cr on cr.id = cc.customer_relationship_id
full join hive.edu_dwd.hcx_dim_dwd_customer_appeal app on cr.id = app.customer_relationship_first_id;


”